package com.garlicts.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;

import com.garlicts.excel.context.ApplicationContextHelper;
import com.garlicts.excel.util.ExcelOperator;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSON;
import com.garlicts.excel.util.FileUtil;
import com.garlicts.excel.util.MapUtil;
import com.garlicts.excel.util.ReflectUtil;
import org.springframework.context.ApplicationContext;

/**
 *	excel导入导出主类
 *	@author wangwei
 */
public class GarlictsExcel<T> {

	private Logger logger = LoggerFactory.getLogger(GarlictsExcel.class);

	/**
	 * 工作簿对象
	 */
	private Workbook wb;
	/**
	 * excel类型枚举
	 */
	ExcelEnum excelEnum;
	/**
	 *	工作表对象
	 */
	private Sheet sheet;
	/**
	 *	标题行号
	 */
	private int headerNum;
	/**
	 * 最后一行的行号
	 */
	private Integer lastDataRowNum;
	/**
	 * 单元格样式
	 */
	CellStyleFactory cellStyleFactory;
	/**
	 * 批注
	 */
	ClientAnchorFactory clientAnchorFactory;
	/**
	 * 富文本
	 */
	RichTextStringFactory richTextStringFactory;
	/**
	 *
	 * k=fieldName, v=列的索引值
	 */
	private Map<String, Integer> fieldColumnMap = new HashMap<String, Integer>();
	/**
	 *
	 * k=列的索引值, v=fieldName
	 */
	private Map<Integer, String> columnFieldMap = new HashMap<Integer, String>();
	/**
	 *	校验的错误信息
	 *	k=行号+"_"+列号, v=错误信息
	 */
	private Map<String, List<String>> verifyErrorMap = new HashMap<String, List<String>>();
	private GarlictsExcelVerifyHandler<T> iExcelVerifyHandler;
	/**
	 * spring上下文
	 */
	private ApplicationContext applicationContext;
	/**
	 * 标题行校验的错误信息
	 */
	private String headerRowVerifyErrorMsg;


	public GarlictsExcel(String absoluteFilePath, int headNum) throws Exception {
		this(absoluteFilePath, headNum, null);
	}

	public GarlictsExcel(String absoluteFilePath, int headNum, GarlictsExcelVerifyHandler<T> iExcelVerifyHandler) throws Exception {
		this(absoluteFilePath, headNum, 0, iExcelVerifyHandler);
	}

	public GarlictsExcel(InputStream inputStream, ExcelEnum excelEnum, int headerNum, GarlictsExcelVerifyHandler<T> iExcelVerifyHandler) throws Exception {
		this(inputStream, excelEnum, headerNum, 0, iExcelVerifyHandler);
	}

	public GarlictsExcel(InputStream inputStream, ExcelEnum excelEnum, int headerNum, int sheetIndex, GarlictsExcelVerifyHandler<T> iExcelVerifyHandler) throws Exception {

		if(Objects.equals(ExcelEnum.EXCEL_2003.getCode(), excelEnum.getCode())) {
			this.wb = new HSSFWorkbook(inputStream);
			this.excelEnum = excelEnum;
		}
		else if(Objects.equals(ExcelEnum.EXCEL_2007.getCode(), excelEnum.getCode())) {
			this.wb = new XSSFWorkbook(inputStream);
			this.excelEnum = excelEnum;
		}

		if(this.wb.getNumberOfSheets() < sheetIndex) {
			logger.error("没有该工作表" + sheetIndex);
			throw new Exception("没有该工作表" + sheetIndex);
		}

		this.sheet = this.wb.getSheetAt(sheetIndex);
		this.headerNum = headerNum;
		this.cellStyleFactory = new CellStyleFactory(this.wb);
		this.clientAnchorFactory = new ClientAnchorFactory();
		this.richTextStringFactory = new RichTextStringFactory();
		this.iExcelVerifyHandler = iExcelVerifyHandler;
		this.applicationContext = ApplicationContextHelper.getApplicationContext();

	}

	/**
	 *	构造函数
	 *	@param absoluteFilePath 文件路径
	 *	@param headerNum 标题行号，数据行号=标题行号+1
	 * @throws Exception
	 */
	public GarlictsExcel(String absoluteFilePath, int headerNum, int sheetIndex, GarlictsExcelVerifyHandler<T> iExcelVerifyHandler) throws Exception {

		FileInputStream inputStream = null;

		if(StringUtils.isBlank(absoluteFilePath)) {
			logger.error("导入的文件路径file为空");
			throw new Exception("导入的文件路径file为空");
		}else {

			File file = new File(absoluteFilePath);
			if(!file.exists()) {
				logger.error("导入的文件不存在");
				throw new Exception("导入的文件不存在");
			}

			inputStream = new FileInputStream(new File(absoluteFilePath));

		}

		if(absoluteFilePath.toLowerCase().endsWith(".xls")) {
			this.wb = new HSSFWorkbook(inputStream);
			this.excelEnum = ExcelEnum.EXCEL_2003;
		}else if(absoluteFilePath.toLowerCase().endsWith(".xlsx")) {
			this.wb = new XSSFWorkbook(inputStream);
			this.excelEnum = ExcelEnum.EXCEL_2007;
		}else {
			logger.error("导入的文件格式不支持");
			throw new Exception("导入的文件格式不支持");
		}

		if(this.wb.getNumberOfSheets() < sheetIndex) {
			logger.error("没有该工作表" + sheetIndex);
			throw new Exception("没有该工作表" + sheetIndex);
		}

		this.sheet = this.wb.getSheetAt(sheetIndex);
		this.headerNum = headerNum;
		this.cellStyleFactory = new CellStyleFactory(this.wb);
		this.clientAnchorFactory = new ClientAnchorFactory();
		this.richTextStringFactory = new RichTextStringFactory();
		this.iExcelVerifyHandler = iExcelVerifyHandler;
		this.applicationContext = ApplicationContextHelper.getApplicationContext();

	}

	/**
	 *	获取导入数据列表
	 */
	private List<T> getDataList(Class<T> clazz){

		List<T> dataList = new ArrayList<T>();

		List<Map<String, Object>> dataMapList = new ArrayList<Map<String, Object>>();

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		// field k=列名称，v=fieldName
		Map<String, String> fieldMap = new HashMap<String, String>();
		// 标题行 k=列的索引位置， v=列名称
		Map<Integer, String> headMap = new HashMap<Integer, String>();
		// k=列的索引位置, v=字段的值
//		List<Map<Integer, Object>> dataMapList = new ArrayList<Map<Integer, Object>>();

//		Row headerRow = this.getRow(this.headerNum);

		Row headerRow = ExcelOperator.getRow(this.sheet, this.headerNum);

		short headerCellNum = headerRow.getLastCellNum();
		for(int i=0; i<headerCellNum; i++) {

//			String headColumnName = (String) this.getCellValue(headerRow, i);

			String headColumnName = (String) ExcelOperator.getCellValue(headerRow, i);

			headMap.put(i, headColumnName);

		}

		Field[] declaredFields;

		try {
			declaredFields = clazz.getDeclaredFields();
			for(int j = 0; j < declaredFields.length; j++) {
				Field field = declaredFields[j];
				field.setAccessible(true);

				ExcelField excelAnnotation = field.getAnnotation(ExcelField.class);
				String name = excelAnnotation.name();
				if(StringUtils.isNotBlank(name)) {
					fieldMap.put(name, field.getName());
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		for(int i = ExcelOperator.getDataRowNum(this.headerNum); i <= ExcelOperator.getLastDataRowNum(this.sheet); i++) {

//			Row row = this.getRow(i);
			Row row = ExcelOperator.getRow(this.sheet, i);

			// 空行跳出循环
			if(isEmptyRow(row)) {
				// 设置最后一行的真实行号
				lastDataRowNum = i - 1;
				break;
			}

			Map<String, Object> map = new HashMap<String, Object>();

			for(int j = 0; j < headerCellNum; j++) {

				String headName = headMap.get(j);
//				Object cellValue = this.getCellValue(row, j);
				Object cellValue = ExcelOperator.getCellValue(row, j);

				// k=列名称， v=单元格的值
				map.put(headName, cellValue);

				String fieldName = fieldMap.get(headName);
				fieldColumnMap.put(fieldName, j);

				columnFieldMap.put(j, fieldName);

			}
			list.add(map);

		}

		for(Map<String,Object> m : list) {

			Map<String, Object> dataMap = new HashMap<String, Object>();

			// 列名称集合
			Set<String> keySet = m.keySet();
			for(String headName : keySet) {

				//通过headName获取fieldName
				String fieldName = fieldMap.get(headName);

				// 通过headName获取单元格的值
				Object val = m.get(headName);

				dataMap.put(fieldName, val);

			}

			dataMapList.add(dataMap);

		}


		for(Map<String,Object> dataMap : dataMapList) {

			try {
				T t = (T) MapUtil.map2Javabean(dataMap, clazz);
				dataList.add(t);
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		for(int i=0; i<dataList.size(); i++) {
			T t = dataList.get(i);

			Map<String, Field> fieldMap2 = ReflectUtil.getFieldMap(t);
			Field field = fieldMap2.get("rowNum");
			field.setAccessible(true);
			try {
				field.set(t, Integer.valueOf(headerNum + i + 1));
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return dataList;

	}

	/**
	 * 是否为空行
	 * @param row
	 * @return
	 */
	private boolean isEmptyRow(Row row) {

		if(row == null) {
			return true;
		}

//		int lastCellNum = getLastDataCellNum();
		int lastCellNum = ExcelOperator.getLastDataCellNum(this.sheet, headerNum);

		for(int j=0; j<lastCellNum; j++) {

			Cell cell = row.getCell(j);
			if(cell != null && !Objects.equals(cell.getCellType(), Cell.CELL_TYPE_BLANK)) {
				return false;
			}

		}

		return true;

	}

	/**
	 * 导入excel
	 */
	public GarlictsImportResult<T> importExcel(Class<T> clazz) {

		GarlictsImportResult<T> importResult = new GarlictsImportResult<T>();

		// 标题行校验
		importResult = headerRowVerifyHandler(importResult, clazz);

		// 获取excel数据行
		List<T> dataList = getDataList(clazz);
		logger.info("从excel中获取的数据条数：{}", dataList.size());

		importResult.setDataList(dataList);

		// 数据行校验
		importResult = dataRowVerifyAndExecuteHandler(importResult, dataList);

		return importResult;

	}

	/**
	 * 标题行的校验合法性 组装GarlictsImportResult
	 */
	private GarlictsImportResult headerRowVerifyHandler(GarlictsImportResult<T> importResult, Class<T> clazz){

		if(!Objects.equals(iExcelVerifyHandler, null)) {

			// 标题行校验
			String headerRowVerifyErrorMsg = doHeaderRowVerify(this.sheet, this.headerNum, clazz);
			if(StringUtils.isNotBlank(headerRowVerifyErrorMsg)) {
				this.headerRowVerifyErrorMsg = headerRowVerifyErrorMsg;
				importResult.setHeaderRowVerify(false);
			}else {
				importResult.setHeaderRowVerify(true);
			}

		}else {
			importResult.setHeaderRowVerify(true);
		}

		logger.info("标题行校验结果：{}\n标题和校验错误信息：{}", importResult.getHeaderRowVerify(), this.headerRowVerifyErrorMsg);

		return importResult;

	}

	/**
	 * 标题行的校验合法性
	 */
	private String doHeaderRowVerify(Sheet sheet, int headerNum, Class<T> clazz) {

		// excel标题行的列
		List<String> headerColumnList = new ArrayList<String>();
		// excel标题行必须的列
		List<String> requiredHeaderColumnList = new ArrayList<String>();
		// 用户导入的excel标题行
		List<String> userHeaderColumnList = new ArrayList<String>();
		// 错误信息
		StringBuilder errorMsgBuilder = new StringBuilder();

		try {
			Field[] declaredFields = clazz.getDeclaredFields();
			for(int j = 0; j < declaredFields.length; j++) {
				Field field = declaredFields[j];
				field.setAccessible(true);

				ExcelField excelAnnotation = field.getAnnotation(ExcelField.class);
				String name = excelAnnotation.name();
				if(StringUtils.isNotBlank(name)) {

					headerColumnList.add(name);

					boolean required = excelAnnotation.required();
					if(required) {
						requiredHeaderColumnList.add(name);
					}

				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		// 校验多余的列
		Row headerRow = ExcelOperator.getRow(sheet, headerNum);
		short headerCellNum = headerRow.getLastCellNum();
		for(int i=0; i<headerCellNum; i++) {

			String headColumnName = (String) ExcelOperator.getCellValue(headerRow, i);

			if(StringUtils.isNotBlank(headColumnName)) {
				userHeaderColumnList.add(headColumnName);
			}

			if(StringUtils.isNotBlank(headColumnName) && !headerColumnList.contains(headColumnName)) {
				errorMsgBuilder.append("存在多余的列：").append(headColumnName).append("；");
				logger.error("存在多余的列，{}", headColumnName);
			}

		}

		// 校验必须的列
		for(String requiredColumn : requiredHeaderColumnList) {

			if(!userHeaderColumnList.contains(requiredColumn)) {
				errorMsgBuilder.append("必须的列不存在：").append(requiredColumn).append("；");
				logger.error("必须的列不存在，{}", requiredColumn);
			}

		}

		return errorMsgBuilder.toString();

	}

	/**
	 * 数据校验 以及 执行导入 处理器。
	 * @param importResult
	 * @param dataList
	 * @return
	 */
	private GarlictsImportResult dataRowVerifyAndExecuteHandler(GarlictsImportResult<T> importResult, List<T> dataList){

		if(Objects.equals(iExcelVerifyHandler, null)){
			return importResult;
		}

		for(int i = 0; i < dataList.size(); i++) {

			T t = dataList.get(i);
			Map<String, Field> fieldMapTemp = ReflectUtil.getFieldMap(t);

			// 数据行校验并回写校验结果
			boolean dataRowVerify = dataRowVerifyHandler(t, headerNum + (i + 1), fieldColumnMap, fieldMapTemp);
			if(dataRowVerify){
				// 执行导入并回写导入的结果和错误信息
				dataRowImportHandler(t, headerNum + (i + 1), fieldColumnMap, fieldMapTemp);
			}

		}

		importResult.setErrorMap(verifyErrorMap);
		logger.info("自定义校验的错误信息：{}", JSON.toJSONString(verifyErrorMap));
		importResult.setFieldColumnMap(fieldColumnMap);

		return importResult;

	}

	private boolean dataRowVerifyHandler(T t, int currRowNum, Map<String, Integer> fieldColumnMap, Map<String, Field> fieldMap){

		boolean dataRowVerify = true;

		// 校验错误信息map
		Map<String, String> errorMap = iExcelVerifyHandler.verify(t, currRowNum, fieldColumnMap, this.applicationContext);
		// 必填项的校验
		requiredVerify(t, currRowNum, fieldColumnMap, errorMap);

		Field rowVerifyResultField = fieldMap.get("rowVerifyResult");
		rowVerifyResultField.setAccessible(true);

		if(MapUtils.isEmpty(errorMap)){

			try {
				rowVerifyResultField.set(t, true);
			} catch (Exception e) {
				e.printStackTrace();
			}

		}else{

			try {
				rowVerifyResultField.set(t, false);
				dataRowVerify = false;
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		Set<String> keySet = errorMap.keySet();
		for(String s : keySet) {

			List<String> list = verifyErrorMap.get(s);
			if(CollectionUtils.isEmpty(list)) {
				list = new ArrayList<String>();
			}

			list.add(errorMap.get(s));
			verifyErrorMap.put(s, list);

		}

		return dataRowVerify;

	}

	private void requiredVerify(T t, int currRowNum, Map<String, Integer> fieldColumnMap, Map<String, String> errorMap){

		Field[] declaredFields = t.getClass().getDeclaredFields();
		for(int j = 0; j < declaredFields.length; j++) {
			Field field = declaredFields[j];
			field.setAccessible(true);

			ExcelField excelAnnotation = field.getAnnotation(ExcelField.class);
			String name = excelAnnotation.name();
			if(StringUtils.isBlank(name)) {
				continue;
			}

			boolean required = excelAnnotation.required();
			if(!required) {
				continue;
			}

			boolean isNull = false;
			try {
				Object fieldValueObject = field.get(t);
				if(Objects.equals(fieldValueObject, null) || Objects.equals(fieldValueObject, "")){
					isNull = true;
				}
			} catch (IllegalAccessException e) {
				e.printStackTrace();
			}

			if(isNull){
				Integer columnNum = fieldColumnMap.get(field.getName());
				String errorKey = new StringBuilder().append(currRowNum).append("_").append(columnNum).toString();
				errorMap.put(errorKey, new StringBuilder(field.getName()).append("不能为空").toString());
			}

		}

	}

	private void dataRowImportHandler(T t, int currRowNum, Map<String, Integer> fieldColumnMap, Map<String, Field> fieldMap){

		// 执行导入
		try {
			// 导入错误信息map
			String importErrorStr = iExcelVerifyHandler.execute(t, currRowNum, fieldColumnMap, this.applicationContext);
			if(StringUtils.isBlank(importErrorStr)){
				Field rowImportResultField = fieldMap.get("rowImportResult");
				rowImportResultField.setAccessible(true);
				rowImportResultField.set(t, true);
			}else{
				Field rowImportErrorMsgField = fieldMap.get("rowImportErrorMsg");
				rowImportErrorMsgField.setAccessible(true);
				rowImportErrorMsgField.set(t, importErrorStr);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

//	private String map2String(Map<String, String> errorMap){
//
//		if(MapUtils.isEmpty(errorMap)){
//			return null;
//		}
//
//		StringBuilder stringBuilder = new StringBuilder();
//		Set<String> strings = errorMap.keySet();
//		for(String s : strings){
//			stringBuilder.append(errorMap.get(s)).append(";");
//		}
//
//		return stringBuilder.toString();
//
//	}

	/**
	 * 导出excel
	 * @param dataList 数据list
	 * @param exportPath 要导出的绝对路径，例如"D:/export/error_excel.xls"
	 */
	public void exportExcel(List<T> dataList, String exportPath) {

		for(int i=0; i<dataList.size(); i++) {

			int rowNum = headerNum + i + 1;

			Row row = this.sheet.createRow(rowNum);
//			int lastDataCellNum = getLastDataCellNum();
			int lastDataCellNum = ExcelOperator.getLastDataCellNum(this.sheet, this.headerNum);

			T t = dataList.get(i);
			Class<? extends Object> clazz = t.getClass();

			for(int columnNum=0; columnNum<lastDataCellNum; columnNum++) {

				Cell cell = row.createCell(columnNum);
				String rowColumnNum = new StringBuilder().append(rowNum).append("_").append(columnNum).toString();

				String fieldName = columnFieldMap.get(columnNum);
				try {

					if(StringUtils.isBlank(fieldName)) {
						continue;
					}
					Field declaredField = clazz.getDeclaredField(fieldName);
					declaredField.setAccessible(true);
					Object value = declaredField.get(t);
					if(value instanceof Date) {
						SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");
						String format = simpleDateFormat.format(value);
						cell.setCellValue(format);
					}else {
						cell.setCellValue(value == null ? "" : value.toString());
					}

					setCellErrorStyle(cell, rowColumnNum);

				} catch (Exception e) {
					logger.error("创建excel单元格发生异常", e);
				}


			}

		}

		// 创建文件以及目录
		FileUtil.createFile(exportPath);

		FileOutputStream fileOutputStream = null;
		try {
			fileOutputStream = new FileOutputStream(exportPath);
			this.wb.write(fileOutputStream);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			try {
				fileOutputStream.flush();
			} catch (IOException e1) {
				e1.printStackTrace();
			}

			if(fileOutputStream != null) {
				try {
					fileOutputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}

		}



	}

	private void setCellErrorStyle(Cell cell, String rowColumnNum) throws Exception{

		List<String> errorMsgList = verifyErrorMap.get(rowColumnNum);

		if(CollectionUtils.isNotEmpty(errorMsgList)) {

			// 移除链接
			cell.removeHyperlink();

			String errorMsg = "";
			for(String s : errorMsgList) {
				errorMsg = new StringBuilder().append(errorMsg).append(s).append("；").toString();
			}

			// 设置错误信息的批注
			Drawing drawingPatriarch = this.sheet.createDrawingPatriarch();
			ClientAnchor clientAnchor = clientAnchorFactory.getClientAnchor(excelEnum);
			if(Objects.equals(clientAnchor, null)) {
				logger.error("没有合适Workbook，创建批注失败");
				throw new Exception("没有合适Workbook，创建批注失败");
			}
			Comment cellComment = drawingPatriarch.createCellComment(clientAnchor);
			RichTextString richTextString = richTextStringFactory.getRichTextString(excelEnum, errorMsg);
			cellComment.setString(richTextString);
			cell.removeCellComment();
			cell.setCellComment(cellComment);

			// 设置错误样式
			CellStyle errorStyle = cellStyleFactory.getErrorStyle();
			cell.setCellStyle(errorStyle);

		}

	}

}